import xlrd, xlwt
import os
import time
import difflib
import json

class ReadExcel(object):
    def __init__(self, data_path, sheetname,index):
        #定义一个属性接收文件路径
        self.data_path = data_path
        # 定义一个属性接收工作表名称
        self.sheetname = sheetname
        # 使用xlrd模块打开excel表读取数据
        self.data = xlrd.open_workbook(self.data_path)
        # 根据工作表的名称获取工作表中的内容
        self.table = self.data.sheet_by_name(self.sheetname)
        # 获取工作表的有效行数
        self.rowNum = self.table.nrows
        self.index = index

    def getExcelData(self):
        for i in range(1, self.rowNum):
            # 获取单元格数据
            data = self.table.cell_value(i, 6)

            command = self.table.cell_value(i, 0)
            data_list[self.index].append((command, data))
            # print(data_list)      [[(command,data),(command,data)],[],[],[]，[]]

class WriteExcel():
    def __init__(self,sheet_name):
        # 创建一个workbook 设置编码  和worksheet
        self.wb = xlwt.Workbook(encoding='utf-8')
        self.ws = self.wb.add_sheet(sheet_name)

    def write_excel_head(self,head_col0,head_col1,head_col2,head_col3,):
        self.ws.write(0, 0, head_col0)
        self.ws.write(0, 1, head_col1)
        self.ws.write(0, 2, head_col2)
        self.ws.write(0, 3, head_col3)

    def write_excel_body(self, row, col, value):
        # 数据写入excel,参数对应 行, 列, 值
        self.ws.write(row, col, value)  # 不带样式的写入

    def save_excel(self,data_path):
        current_time = time.strftime("%Y-%m-%d_%H_%M_%S", time.localtime(time.time()))
        self.wb.save(data_path +'闲聊内容' + current_time + '.xls')



if __name__ == '__main__':
    data_path = '/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/闲聊/'
    save_path = '/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/闲聊内容/'
    sheetname = '闲聊类指令'

    # 获取excel数据
    files_list = os.listdir(data_path)
    data_list = [[] for i in range(len(files_list))]
    for file_name in files_list:
        read_obj = ReadExcel(data_path+file_name, sheetname, files_list.index(file_name))
        read_obj.getExcelData()
    # print(data_list)    # [  [(command, data), (command, data),(command, data)], [], [], []，[]   ]

    # 生成excel数据
    write_obj = WriteExcel(sheetname)
    write_obj.write_excel_head("指令","返回的type",'相同字符串','结论')

    # [  [(command, data), (command, data), (command, data)],
    #    [(command, data), (command, data), (command, data)],
    #    [(command, data), (command, data), (command, data)],
    #    [(command, data), (command, data), (command, data)]，
    #    [(command, data), (command, data), (command, data)]
    # ]
    row = 1
    for i in range(len(data_list[0])):
        write_obj.write_excel_body(row+0, 0, data_list[0][i][0])
        write_obj.write_excel_body(row+1, 0, data_list[1][i][0])
        write_obj.write_excel_body(row+2, 0, data_list[2][i][0])
        write_obj.write_excel_body(row+3, 0, data_list[3][i][0])
        write_obj.write_excel_body(row+4, 0, data_list[4][i][0])

        write_obj.write_excel_body(row+0, 1, data_list[0][i][1])
        write_obj.write_excel_body(row+1, 1, data_list[1][i][1])
        write_obj.write_excel_body(row+2, 1, data_list[2][i][1])
        write_obj.write_excel_body(row+3, 1, data_list[3][i][1])
        write_obj.write_excel_body(row+4, 1, data_list[4][i][1])

        # 单独获取chat部分
        try:
            chat_data1 = str(data_list[0][i][1]['chat'])
            print(type(data_list[0][i][1]))
            chat_data2 = str(data_list[1][i][1]['chat'])
            chat_data3 = str(data_list[2][i][1]['chat'])
            chat_data4 = str(data_list[3][i][1]['chat'])
            chat_data5 = str(data_list[4][i][1]['chat'])
        except Exception as e:
            chat_data1 = '非闲聊指令1'
            chat_data2 = '非闲聊指令2'
            chat_data3 = '非闲聊指令3'
            chat_data4 = '非闲聊指令4'
            chat_data5 = '非闲聊指令5'

        write_obj.write_excel_body(row + 0, 2, chat_data1)
        write_obj.write_excel_body(row + 1, 2, chat_data2)
        write_obj.write_excel_body(row + 2, 2, chat_data3)
        write_obj.write_excel_body(row + 3, 2, chat_data4)
        write_obj.write_excel_body(row + 4, 2, chat_data5)
        if chat_data1 == chat_data2 == chat_data3 == chat_data4 == chat_data5:
            write_obj.write_excel_body(row + 0, 3, 'PASS')
            write_obj.write_excel_body(row + 1, 3, 'PASS')
            write_obj.write_excel_body(row + 2, 3, 'PASS')
            write_obj.write_excel_body(row + 3, 3, 'PASS')
            write_obj.write_excel_body(row + 4, 3, 'PASS')
        else:
            write_obj.write_excel_body(row + 0, 3, 'FAIL')
            write_obj.write_excel_body(row + 1, 3, 'FAIL')
            write_obj.write_excel_body(row + 2, 3, 'FAIL')
            write_obj.write_excel_body(row + 3, 3, 'FAIL')
            write_obj.write_excel_body(row + 4, 3, 'FAIL')
        # s1 = chat_data1
        # s2 = chat_data2
        #
        # s = difflib.SequenceMatcher(None, s1, s2)
        # res = ""
        # for tag, i1, i2, j1, j2 in s.get_opcodes():
        #     if tag == "equal":
        #         res += s1[i1:i2]
        # print(res)


        row = row+5
    write_obj.save_excel(save_path)







